package com.lipaluma.test.db.internal;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import org.apache.commons.lang.StringUtils;

import com.lipaluma.test.db.exceptions.CleanException;
import com.lipaluma.test.db.internal.loadData.LoadDataInfileParams;

/** 
 * Dialect managed used to define the Truncate request or the load data infile request.
 * @author Mario
 */
public enum DialectEnum {
	MYSQL {
		@Override
		public void doCleanTableByTruncate(Connection connection, String schema, String table) {
			try {
				Statement st = connection.createStatement();
				st.addBatch("SET FOREIGN_KEY_CHECKS = 0");
				if(StringUtils.isNotBlank(schema)) 
					st.addBatch("TRUNCATE TABLE "+schema+"."+table+";");
				else
					st.addBatch("TRUNCATE TABLE "+table+";");
				st.addBatch("SET FOREIGN_KEY_CHECKS = 1");
				st.executeBatch();
			} catch (SQLException e) {
				throw new CleanException("erreur lors du clean de la table "+table, e);
			}
		}

		public String generateLoadDataInfileRequest(File dataFile, LoadDataInfileParams loadData) throws FileNotFoundException {
			String tableName = dataFile.getName().substring(0, dataFile.getName().lastIndexOf('.'));
			String request = "LOAD DATA LOCAL INFILE '"+dataFile.getAbsolutePath().replace("\\", "/")+"' INTO TABLE "+tableName+" ";
			if(StringUtils.isNotEmpty(loadData.getFieldDelimiter()) || StringUtils.isNotEmpty(loadData.getFieldEnclosedBy())) {
				request += "fields ";
				if(StringUtils.isNotEmpty(loadData.getFieldDelimiter()))
					request += "terminated by '"+loadData.getFieldDelimiter()+"' ";
				if(StringUtils.isNotEmpty(loadData.getFieldEnclosedBy()))
					request += "optionally enclosed by '"+loadData.getFieldEnclosedBy()+"' ";
			}
			if(StringUtils.isNotEmpty(loadData.getLinesDelimiter()))
				request += "lines terminated by '"+loadData.getLinesDelimiter()+"' ";
			if(loadData.hasHeaders()) {
				Scanner scan = null;
				try {
					scan = new Scanner(dataFile);				
					String headers = scan.nextLine();
					request += "IGNORE 1 LINES ("+headers.replace(loadData.getFieldDelimiter(), ",")+") ";
				}finally {
					if(scan != null)
						scan.close();
				}
			}
			request+=";";
			return request;
		}

	}, POSTGRE("PostgreSQL") {
		@Override
		public void doCleanTableByTruncate(Connection connection, String schema, String table) {
			try {
				Statement st = connection.createStatement();
				if(StringUtils.isNotBlank(schema)) 
					st.addBatch("TRUNCATE TABLE "+schema+"."+table+";");
				else
					st.addBatch("TRUNCATE TABLE "+table+";");
				st.executeBatch();
			} catch (SQLException e) {
				throw new CleanException("erreur lors du clean de la table "+table, e);
			}
		}

		public String generateLoadDataInfileRequest(File dataFile, LoadDataInfileParams loadData) throws FileNotFoundException {
			String tableName = dataFile.getName().substring(0, dataFile.getName().lastIndexOf('.'));
			String request = "COPY "+tableName+" ";
			if(loadData.hasHeaders()) {
				Scanner scan = null;
				try {
					scan = new Scanner(dataFile);				
					String headers = scan.nextLine();
					request += "("+headers+") ";
				}finally {
					if(scan != null)
						scan.close();
				}
			}
			request += "FROM '"+dataFile.getAbsolutePath().replace("\\", "/")+"' ";
			if(loadData.hasHeaders() || StringUtils.isNotEmpty(loadData.getFieldDelimiter())) {
				request += "WITH NULL AS '\\N' ";
				if(StringUtils.isNotEmpty(loadData.getFieldDelimiter()))
					request += "DELIMITER AS '"+loadData.getFieldDelimiter()+"' ";
				if(loadData.hasHeaders())
					request += "CSV HEADER "; 
			}
			request+=";";
			return request;
		}
		
	}, H2 {
		@Override
		public void doCleanTableByTruncate(Connection connection, String schema, String table) {
			try {
				Statement st = connection.createStatement();
				if(StringUtils.isNotBlank(schema)) {
					st.addBatch("ALTER TABLE "+schema+"."+table+" SET REFERENTIAL_INTEGRITY	FALSE;");
					st.addBatch("TRUNCATE TABLE "+schema+"."+table+";");
					st.addBatch("ALTER TABLE "+schema+"."+table+" SET REFERENTIAL_INTEGRITY	TRUE;");
				} else {
					st.addBatch("ALTER TABLE "+table+" SET REFERENTIAL_INTEGRITY FALSE;");
					st.addBatch("TRUNCATE TABLE "+table+";");
					st.addBatch("ALTER TABLE "+table+" SET REFERENTIAL_INTEGRITY TRUE;");
				}
				st.executeBatch();
			} catch (SQLException e) {
				throw new CleanException("erreur lors du clean de la table "+table, e);
			}
		}

		public String generateLoadDataInfileRequest(File dataFile, LoadDataInfileParams loadData) throws FileNotFoundException {
			String tableName = dataFile.getName().substring(0, dataFile.getName().lastIndexOf('.'));
			String request = "INSERT INTO "+tableName+" ";
			if(loadData.hasHeaders()) {
				Scanner scan = null;
				try {
					scan = new Scanner(dataFile);				
					String headers = scan.nextLine();
					request += "("+headers.replace(loadData.getFieldDelimiter(), ",")+") ";
				}finally {
					if(scan != null)
						scan.close();
				}
				request += " SELECT * FROM CSVREAD('"+dataFile.getAbsolutePath().replace("\\", "/")+"', null, '";
				if(StringUtils.isNotEmpty(loadData.getFieldDelimiter()))
					request+= " rowSeparator="+loadData.getFieldDelimiter();
				if(StringUtils.isNotEmpty(loadData.getLinesDelimiter()))
					request += " lineSeparator="+loadData.getLinesDelimiter();
				if(StringUtils.isNotEmpty(loadData.getFieldEnclosedBy()))
					request += " escape="+loadData.getFieldEnclosedBy();
				request+= "')";
			}
			return request;
		}
	};
	private String[] aliases;
	private DialectEnum() {
		aliases = new String[]{this.name()};
	}
	private DialectEnum(String... aliases) {
		this.aliases = new String[aliases.length+1];
		this.aliases[0] = this.name();
		System.arraycopy(aliases, 0, this.aliases, 1, aliases.length);
	}

	public static DialectEnum getDialect(String value) {
		if(StringUtils.isBlank(value))
			return null;
		for (DialectEnum dialect : values()) {
			for (String alias : dialect.aliases) {
				if(value.toUpperCase().contains(alias.toUpperCase()))
					return dialect;
			}
		}
		return null;
	}
	
	public abstract String generateLoadDataInfileRequest(File dataFile, LoadDataInfileParams loadData) throws FileNotFoundException;

	public abstract void doCleanTableByTruncate(Connection connection, String schema, String table);
}

